Salesforce Connector STRING Functions

ASCII(character_expression)

Returns the ASCII code value of the left-most character of the character expression.

SELECT ASCII('0');
-- Result: 48

CHAR(integer_expression)

Converts the integer ASCII code to the corresponding character.

SELECT CHAR(48);
-- Result: '0'

CHARINDEX(expressionToFind, expressionToSearch[, start_location])

Returns the starting position of the specified expression in the character string.

SELECT CHARINDEX('456', '0123456');
-- Result: 4

SELECT CHARINDEX('456', '0123456', 5);
-- Result: -1

CONCAT(string_value1, string_value2 [, string_valueN])

Returns the string that is the concatenation of two or more string values.


SELECT CONCAT('Hello, ', 'world!');
-- Result: 'Hello, world!'

CONTAINS(expressionToSearch, expressionToFind)

Returns 1 if expressionToFind is found within expressionToSearch; otherwise, 0.


SELECT CONTAINS('0123456', '456');
-- Result: 1

SELECT CONTAINS('0123456', 'Not a number');
-- Result: 0

ENDSWITH(character_expression, character_suffix)

Returns 1 if character_expression ends with character_suffix; otherwise, 0.


SELECT ENDSWITH('0123456', '456');
-- Result: 1

SELECT ENDSWITH('0123456', '012');
-- Result: 0

FORMAT(value, format)

Returns the value formatted with the specified format.


SELECT FORMAT(12.34, '#');
-- Result: 12

SELECT FORMAT(12.34, '#.###');
-- Result: 12.34

SELECT FORMAT(12.34, '000');
-- Result: 012

SELECT FORMAT(12.34, '000.000');
-- Result: 012.340

SELECT FORMAT(1234, '0.000E0');
-- Result: 1.234E3

FROM_UNIXTIME(time, issecond)

Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS expressed in the current time zone.


SELECT FROM_UNIXTIME(1540495231, 1);
-- Result: 2018-10-25 19:20:31

SELECT FROM_UNIXTIME(1540495357385, 0);
-- Result: 2018-10-25 19:22:37

INDEXOF(expressionToSearch, expressionToFind[, start_location])

Returns the starting position of the specified expression in the character string.


SELECT INDEXOF('0123456', '456');
-- Result: 4

SELECT INDEXOF('0123456', '456', 5);
-- Result: -1

ISNULL(check_expression, replacement_value)

Replaces null with the specified replacement value.


SELECT ISNULL(42, 'Was NULL');
-- Result: 42

SELECT ISNULL(NULL, 'Was NULL');
-- Result: 'Was NULL'

JSON_AVG(json, jsonpath)

Computes the average value of a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.


SELECT JSON_AVG('[1,2,3,4,5]', '[x]');
-- Result: 3

SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', 'test.data[x]');
-- Result: 3

SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', 'test.data[3..]');
-- Result: 4.5

JSON_COUNT(json, jsonpath)

Returns the number of elements in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.


SELECT JSON_COUNT('[1,2,3,4,5]', '[x]');
-- Result: 5

SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', 'test.data[x]');
-- Result: 5

SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', 'test.data[3..]');
-- Result: 2

JSON_EXTRACT(json, jsonpath)

Selects any value in a JSON array or object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.


SELECT JSON_EXTRACT('{"test": {"data": 1}}', 'test');
-- Result: '{"data":1}'

SELECT JSON_EXTRACT('{"test": {"data": 1}}', 'test.data');
-- Result: 1

SELECT JSON_EXTRACT('{"test": {"data": [1, 2, 3]}}', 'test.data[1]');
-- Result: 2

JSON_MAX(json, jsonpath)

Gets the maximum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.


SELECT JSON_MAX('[1,2,3,4,5]', '[x]');
-- Result: 5

SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', 'test.data[x]');
-- Result: 5

SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', 'test.data[..3]');
-- Result: 4

JSON_MIN(json, jsonpath)

Gets the minimum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.


SELECT JSON_MIN('[1,2,3,4,5]', '[x]');
-- Result: 1

SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', 'test.data[x]');
-- Result: 1

SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', 'test.data[3..]');
-- Result: 4

JSON_SUM(json, jsonpath)

Computes the summary value in JSON according to the JSONPath expression. Return value is numeric or null.


SELECT JSON_SUM('[1,2,3,4,5]', '[x]');
-- Result: 15

SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', 'test.data[x]');
-- Result: 15

SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', 'test.data[3..]');
-- Result: 9

LEFT(character_expression, integer_expression)

Returns the specified number of characters counting from the left of the specified string.


SELECT LEFT('1234567890', 3);
-- Result: '123'

LEN(string_expression)

Returns the number of characters of the specified string expression.


SELECT LEN('12345');
-- Result: 5

LOWER(character_expression)

Returns the character expression with the uppercase character data converted to lowercase.


SELECT LOWER('MIXED case');
-- Result: 'mixed case'

LTRIM(character_expression)

Returns the character expression with leading blanks removed.


SELECT LTRIM('      trimmed');
-- Result: 'trimmed'

NCHAR(integer_expression)

Returns the Unicode character with the specified integer code as defined by the Unicode standard.

PATINDEX(pattern, expression)

Returns the starting position of the first occurrence of the pattern in the expression. Returns 0 if the pattern is not found.


SELECT PATINDEX('123%', '1234567890');
-- Result: 1

SELECT PATINDEX('%890', '1234567890');
-- Result: 8

SELECT PATINDEX('%456%', '1234567890');
-- Result: 4

QUOTENAME(character_string[, quote_character])

Returns a valid SQL Server-delimited identifier by adding the necessary delimiters to the specified Unicode string.


SELECT QUOTENAME('table_name');
-- Result: '[table_name]'

SELECT QUOTENAME('table_name', '"');
-- Result: '"table_name"'

SELECT QUOTENAME('table_name', '[');
-- Result: '[table_name]'

REPLACE(string_expression, string_pattern, string_replacement)

Replaces all occurrences of a string with another string.


SELECT REPLACE('1234567890', '456', '|');
-- Result: '123|7890'

SELECT REPLACE('123123123', '123', '.');
-- Result: '...'

SELECT REPLACE('1234567890', 'a', 'b');
-- Result: '1234567890'

REPLICATE(string_expression, integer_expression)

Repeats the string value the specified number of times.


SELECT REPLACE('x', 5);
-- Result: 'xxxxx'

REVERSE(string_expression)

Returns the reverse order of the string expression.


SELECT REVERSE('1234567890');
-- Result: '0987654321'

RIGHT(character_expression, integer_expression)

Returns the right part of the string with the specified number of characters.


SELECT RIGHT('1234567890', 3);
-- Result: '890'

RTRIM(character_expression)

Returns the character expression after it removes trailing blanks.


SELECT RTRIM('trimmed          ');
-- Result: 'trimmed'

SOUNDEX(character_expression)

Returns the four-character Soundex code, based on how the string sounds when spoken.


SELECT SOUNDEX('smith');
-- Result: 'S530'

SPACE(repeatcount)

Returns the string that consists of repeated spaces.


SELECT SPACE(5);
-- Result: '     '

STARTSWITH(character_expression, character_prefix)

Returns 1 if character_expression starts with character_prefix; otherwise, 0.


SELECT STARTSWITH('0123456', '012');
-- Result: 1

SELECT STARTSWITH('0123456', '456');
-- Result: 0

STR(float_expression[, integer_length[, integer_decimal]])

Returns the character data converted from the numeric data. For example, STR(123.45, 6, 1) returns 123.5.


SELECT STR('123.456');
-- Result: '123'

SELECT STR('123.456', 2);
-- Result: '**'

SELECT STR('123.456', 10, 2);
-- Result: '123.46'

STUFF(character_expression, integer_start, integer_length, replaceWith_expression)

Inserts a string into another string. It deletes the specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.


SELECT STUFF('1234567890', 3, 2, 'xx');
-- Result: '12xx567890'

SUBSTRING(expression, integer_start, integer_length)

Returns the part of the string with the specified length; starts at the specified index.


SELECT SUBSTRING('1234567890', 3, 2);
-- Result: '34'

TOSTRING(string_value1)

Converts the value of this instance to its equivalent string representation.


SELECT TOSTRING(123);
-- Result: '123'

SELECT TOSTRING(123.456);
-- Result: '123.456'

SELECT TOSTRING(null);
-- Result: ''

TRIM(character_expression)

Returns the character expression with leading and trailing blanks removed.


SELECT TRIM('     trimmed     ');
-- Result: 'trimmed'

UNICODE(ncharacter_expression)

Returns the integer value defined by the Unicode standard of the first character of the input expression.

UPPER(character_expression)

Returns the character expression with lowercase character data converted to uppercase.


SELECT UPPER('MIXED case');
-- Result: 'MIXED CASE'

XML_EXTRACT(xml, xpath[, separator])

Extracts an XML document using the specified XPath to flatten the XML. A comma is used to separate the outputs by default, but this can be changed by specifying the third parameter.


SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch');
-- Result: 'a,e,i,o,u'

SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch', ';');
-- Result: 'a;e;i;o;u'